﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace PlsqlFormat
{
    public class SQLFormatter
    {
        int count;

        public string Excute(string sql, int count = 1)
        {
            sql = Regex.Replace(sql, "//*.*?/*/", "");
            sql =sql.Substring(0, sql.IndexOf(";"));
            this.count = count < 1 ? 1 : count;
            var method = sql.Substring(0, sql.IndexOf(" ")).ToLower().Trim();
            method = method[0].ToString().ToUpper() + method.Substring(1) + "Sql";
            var param = new object[] { sql };
            param = method.Equals("SelectSql") ? new object[] { sql, false } : param;
            var str = GetType().GetMethod(method).Invoke(this, param) as string;
            return str + ";\n";
        }

        public string SelectSql(string sql, bool IsSub = false)
        {
            if (IsSub) count++;
            if (sql.ContainsNumIgnoreCase("\\|\\|") > 100) return sql;
            var str = $"{WhiteSpace}SELECT \n";
            if (sql.ContainsIgnoreCase("UNION"))
            {
                var arr = sql.SplitIgnoreCase("union");
                if (IsFullStr(arr[0]))
                {
                    str = "";
                    for (int i = 0; i < arr.Length; i++)
                    {
                        var sel = arr[i].Trim();
                        var union = $"\n{WhiteSpace}UNION \n";
                        if (sel.StartsWithIgnoreCase("all "))
                        {
                            sel = sel.ReplaceIgnoreCase("all ", "");
                            union = $"\n{WhiteSpace}UNION ALL \n";
                        }
                        if (i == 0) union = "";
                        str += $"{union}{SelectSql(sel)}";
                    }
                    return str;
                }
            }
            str += $"{SelectCols(sql, out string tbls)} \n";
            str += TblsSql(tbls, out string where);
            str += WhereSql(where);
            if (IsSub) count--;
            return str;
        }

        public string UpdateSql(string sql)
        {
            var head = sql.Substring(0, sql.IndexOfIgnoreCase("set ") + 3);
            sql = sql.Replace(head, "").Trim();
            string str = WhiteSpace + head + "\n";
            str += UpdateCols(sql);
            return str;
        }

        public string InsertSql(string sql)
        {
            int idx = sql.IndexOf("(") + 1;
            int num = sql.IndexOfIgnoreCase("select ");
            if (num != -1 && num < idx) idx = num;
            var head = sql.Substring(0, idx);
            if (head.ContainsIgnoreCase("all ")) return sql;
            if (head.ContainsIgnoreCase("values "))
            {
                head = head.Substring(0, head.IndexOfIgnoreCase("values"));
            }
            sql = sql.Replace(head, "").Trim();
            string str = WhiteSpace + head + "\n";
            str += InsertCols(sql);
            return str;
        }

        public string DeleteSql(string sql)
        {
            var idx = sql.IndexOfIgnoreCase("where ");
            if (idx == -1)
            {
                return WhiteSpace + sql;
            }
            else
            {
                var head = sql.Substring(0, idx);
                sql = sql.Replace(head, "").Trim();
                var str = WhiteSpace + head + WhereSql(sql);
                return str;
            }
        }

        public string LengthFomart(string sql, int count, bool IsSub = false)
        {
            this.count = count < 1 ? 1 : count;
            if ((sql.EndsWith(");") && IsFullStr(sql)) || IsSub)
            {
                var str = sql.Substring(0, sql.IndexOf("(") + 1);
                sql = sql.Replace(str, "").Trim();
                sql = sql.Substring(0, sql.LastIndexOf(")"));
                str = WhiteSpace + str + "\n";
                var arr = SplitContainsQuotation(sql, true);
                str += AddCols(null, arr);
                str += IsSub ? $"\n{WhiteSpace})\n" : $"\n{WhiteSpace});\n";
                return str;
            }
            return WhiteSpace + sql;
        }

        private string InsertCols(string sql)
        {
            var str = "";
            if (!sql.StartsWithIgnoreCase("values") && sql.ContainsIgnoreCase("values"))
            {
                var param = sql.Substring(0, sql.IndexOfIgnoreCase("values"));
                str += AddCols(param);
            }
            if (sql.ContainsIgnoreCase("values"))
            {
                str += "\n" + WhiteSpace + "VALUES( \n";
                sql = Regex.Match(sql, "\\(.*\\)").Value;
                sql = sql.Substring(1, sql.Length - 2);
                str += AddCols(sql) + ")";
            }
            else
            {
                var end = "";
                if (sql.StartsWithIgnoreCase("select "))
                {
                    if (sql.EndsWith(")"))
                    {
                        sql = sql.Substring(0, sql.LastIndexOf(")"));
                        end = $"\n{WhiteSpace})";
                    }
                }
                else
                {
                    var sub = sql.Substring(0, sql.IndexOfIgnoreCase("select ")).Trim();
                    sql = sql.Replace(sub, "").Trim();
                    str += AddCols(sub);
                }
                str += $"\n{SelectSql(sql)}{end}";
            }
            return str;
        }

        private string SelectCols(string sql, out string other)
        {
            var str = "";
            var param = sql.Substring(0, sql.IndexOfIgnoreCase(" from"));
            int num = 0;
            while (!IsFullStr(param))
            {
                param = sql.Substring(0, sql.IndexOfCount(" from", ++num));
            }
            other = string.IsNullOrEmpty(param) ? sql : sql.ReplaceFirst(param, "").Trim();
            param = param.ReplaceFirst("select", "").Trim();
            if (string.IsNullOrEmpty(param)) return "";
            var sub = "";
            if (param.ContainsIgnoreCase("into "))
            {
                var into = param.Substring(param.IndexOfIgnoreCase("into ") + 4);
                sub = "INTO \n";
                sub += AddCols(into);
                param = param.Substring(0, param.IndexOfIgnoreCase("into ")).Trim();
            }
            str += AddCols(param);
            if (!string.IsNullOrEmpty(sub)) str += $"\n {WhiteSpace}{sub}";
            return str;
        }

        private string TblsSql(string sql, out string other)
        {
            var str = WhiteSpace + "FROM\n";
            var tbls = sql.Substring(sql.IndexOfIgnoreCase("from") + 4).Trim();
            var group = "";
            var order = "";
            if (tbls.ContainsIgnoreCase("where "))
            {
                int num = 0;
                string temp;
                do
                {
                    temp = tbls.SubStr(0, tbls.IndexOfCount("where ", num++));
                }
                while (!IsFullStr(temp));
                tbls = temp;
            }
            else
            {
                tbls = LimitSql(tbls, out group, out order, out string having, out string limit);
            }
            other = sql.ReplaceFirst(tbls, "").Trim();
            count++;
            if (tbls.ContainsIgnoreCase("join "))
            {
                do
                {
                    var temp = tbls.Substring(0, tbls.IndexOfIgnoreCase("join ") + 4).Trim();
                    while (!IsFullStr(temp) || temp.Length < 16)
                    {
                        tbls = tbls.ReplaceFirst("join ", "temp ");
                        int num = tbls.IndexOfIgnoreCase("join ");
                        temp = num == -1 ? tbls : tbls.Substring(0, num + 4).Trim();
                    }
                    tbls = tbls.Replace("temp ", "JOIN ");
                    temp = temp.Replace("temp ", "JOIN ").Trim();
                    int idx = Math.Max(temp.LastIndexOfIgnoreCase("left ")
                        , Math.Max(temp.LastIndexOfIgnoreCase("right"),
                        temp.LastIndexOfIgnoreCase("inner")));
                    if (temp.EndsWith("JOIN")) temp = temp.Substring(0, idx);
                    if (!temp.ContainsIgnoreCase("select "))
                    {
                        if (temp.ContainsIgnoreCase(" on "))
                        {
                            temp = temp.Substring(0, temp.IndexOfIgnoreCase("on "));
                        }
                        var bengin = str.EndsWith("\n") ? "" : "\n";
                        str += $"{bengin}{WhiteSpace}{temp}\n";
                    }
                    else
                    {
                        int num = count;
                        var start = $"{WhiteSpace}{temp.Substring(0, temp.IndexOf("(") + 1)}\n";
                        if (!str.EndsWith("FROM\n")) start = "\n" + start;
                        str += start;
                        var end = temp.Substring(temp.LastIndexOf(")"));
                        end = OnFormat(end);
                        str += SelectSql(GetTxt(temp), false);
                        count = num;
                        str += $"\n{WhiteSpace}{end}";
                    }
                    if (!string.IsNullOrEmpty(temp)) tbls = tbls.Replace(temp, "").Trim();
                    if (tbls.StartsWithIgnoreCase("on "))
                    {
                        idx = tbls.IndexOfIgnoreCase("join ");
                        var on = tbls.SubStr(0, idx).Trim();
                        idx = Math.Max(on.IndexOfIgnoreCase("left ")
                            , Math.Max(on.IndexOfIgnoreCase("right"),
                            on.IndexOfIgnoreCase("inner")));
                        on = tbls.SubStr(0, idx).Trim();
                        tbls = tbls.Replace(on, "").Trim();
                        str += OnFormat(on);
                    }
                }
                while (!string.IsNullOrEmpty(tbls));
            }
            else
            {
                var arr = SplitContainsQuotation(tbls);              
                for (int i = 0; i < arr.Length; i++)
                {
                    var item = arr[i].Trim();
                    var comma = i == arr.Length - 1 ? "" : ",\n";
                    if (item.ContainsIgnoreCase("select "))
                    {
                        var name = WhiteSpace + item.Substring(item.LastIndexOf(")"));
                        int num = count;
                        item = GetTxt(item);
                        str += $"{WhiteSpace}(\n";
                        item = SelectSql(item);
                        count = num;
                        str += item;
                        str += $"\n{name}{comma}";
                        continue;
                    }
                    str += $"{WhiteSpace}{item}{comma}";
                }
                count--;
            }
            if (!string.IsNullOrEmpty(group)) str += $"\n{WhiteSpace}{GroupOrderFormat(group)}";
            if (!string.IsNullOrEmpty(order)) str += $"\n{WhiteSpace}{GroupOrderFormat(order)}";
            return str;
        }

        private string WhereSql(string sql)
        {
            int num = sql.IndexOfIgnoreCase("where ");
            if (num == -1) return "";
            var where = sql.Substring(num + 5);
            num = 0;
            var str = "\n" + WhiteSpace + "WHERE\n";
            while (!IsFullStr(where))
            {
                int idx = sql.IndexOfCount("where", ++num);
                where = idx == -1 ? "" : sql.Substring(idx + 5);
            }
            if (string.IsNullOrEmpty(where)) return "";
            count++;
            where = LimitSql(where, out string group, out string order, out string having, out string limit);
            var and = WhiteSpace;
            while (!string.IsNullOrEmpty(where.Trim()))
            {
                string sub;
                num = 0;
                do
                {
                    sub = where.SubStr(0, where.IndexOfCount("and ", num++)).Trim();
                }
                while (!IsFullStr(sub));
                where = where.ReplaceFirst(sub, "").ReplaceFirst("and ", "").Trim();
                if (sub.Contains("||")) Console.WriteLine(sub);
                sub = SubSql(sub).Trim();
                var comma = string.IsNullOrEmpty(where) ? "" : "\n";
                str += $"{and} {sub} {comma}";
                and = WhiteSpace + "AND ";
            }
            if (!string.IsNullOrEmpty(group)) str += $"\n{WhiteSpace}{GroupOrderFormat(group)}";
            if (!string.IsNullOrEmpty(having)) str += $"\n{WhiteSpace}{having}";
            if (!string.IsNullOrEmpty(order)) str += $"\n{WhiteSpace}{GroupOrderFormat(order)}";
            if (!string.IsNullOrEmpty(limit)) str += $"\n{WhiteSpace}{limit}";
            count--;
            return str;
        }

        private string LimitSql(string sql, out string group,
           out string order, out string having, out string limit)
        {
            int num;
            group = "";
            order = "";
            having = "";
            limit = "";
            if (sql.ContainsIgnoreCase("group "))
            {
                var sub = sql;
                num = 0;
                do
                {
                    int idx = sub.IndexOfCount("group", num++);
                    group = idx == -1 ? "" : sub.Substring(idx);
                }
                while (!IsFullStr(group));
                if (!string.IsNullOrEmpty(group)) sql = sql.Replace(group, "");
                if (group.ContainsIgnoreCase("order"))
                {
                    order = group.Substring(group.IndexOfIgnoreCase("order"));
                    group = group.Replace(order, "").Trim();
                }
                if (group.ContainsIgnoreCase("having"))
                {
                    having = group.Substring(group.IndexOfIgnoreCase("having"));
                    group = group.Replace(having, "").Trim();
                    having = SubSql(having);
                }
            }
            if (sql.ContainsIgnoreCase("order "))
            {
                var sub = sql;
                num = 0;
                do
                {
                    int idx = sub.IndexOfCount("order", num++);
                    order = idx == -1 ? "" : sub.Substring(idx);
                }
                while (!IsFullStr(order));
                if (!string.IsNullOrEmpty(order)) sql = sql.RelaceLast(order, "");
                if (order.ContainsIgnoreCase("limit "))
                {
                    limit = order.Substring(order.IndexOfIgnoreCase("limit "));
                    order = order.Substring(0, order.IndexOfIgnoreCase("limit "));
                }
            }
            if (sql.ContainsIgnoreCase("limit "))
            {
                limit = sql.Substring(sql.IndexOfIgnoreCase("limit"));
                sql = sql.Replace(limit, "");
            }
            return sql;
        }

        private string SubSql(string sql)
        {
            if (!sql.Contains(")") && !sql.Contains("(") && !sql.ContainsIgnoreCase("select ")) return sql;
            if (sql.ContainsIgnoreCase(" in") && !sql.ContainsIgnoreCase("select ")) return sql;
            if (!sql.ContainsIgnoreCase("select ") && sql.Contains("=") && sql.Length < 120) return sql;
            var end = sql.Substring(sql.LastIndexOf(")"));
            if (end.Length > 50) end = "";
            var str = sql.Substring(0, sql.IndexOf("(") + 1).Trim();
            if (sql.ReplaceFirst(str, "").Trim().Length < 100) return sql;
            sql = sql.ReplaceFirst(str, "").Trim();
            if (!string.IsNullOrEmpty(end)) sql = sql.Substring(0, sql.LastIndexOf(")")).Trim();
            if (!sql.ContainsIgnoreCase("select ") && sql.Length > 100)
            {
                count++;
                str += ComplexSub(sql);
                sql = "";
            }
            if (sql.StartsWithIgnoreCase("select "))
            {
                int num = count;
                str += $"\n{SelectSql(sql, true)}";
                count = num + 1;
            }
            else if (sql.ContainsIgnoreCase("select "))
            {
                count++;
                var start = sql.Substring(0, sql.IndexOfIgnoreCase("select "));
                if (sql.ContainsIgnoreCase(" in") && !start.ContainsIgnoreCase("and ")
                       && !start.ContainsIgnoreCase("or "))
                {
                    str += "\n" + WhiteSpace + sql.Substring(0, sql.IndexOfIgnoreCase("select ")) + "\n";
                    var sub = sql.Substring(sql.IndexOfIgnoreCase("select "));
                    var over = "";
                    if (sub.LastIndexOf(")") != -1)
                    {
                        over = sub.Substring(sub.LastIndexOf(")") + 1).Trim();
                        if (!over.StartsWithIgnoreCase("and") && !over.StartsWithIgnoreCase("or"))
                        {
                            over = "";
                        }
                        else
                        {
                            sub = sub.Substring(0, sub.LastIndexOf(")"));
                        }
                    }             
                    str += SelectSql(sub);
                    if (!string.IsNullOrEmpty(over)) str += $"\n{WhiteSpace})\n{WhiteSpace}{over}";
                    if (string.IsNullOrEmpty(end)) str += $"\n{WhiteSpace})";
                }
                else
                {
                    var templ = sql.ReplaceIgnoreCase("and ", "\n AND ").ReplaceIgnoreCase("or ", "\n OR ");
                    var arr = templ.Split('\n');
                    count++;
                    for (int i = 0; i < arr.Length; i++)
                    {
                        var sub = arr[i];
                        while (!IsFullStr(sub))
                        {
                            if (i < arr.Length - 1) sub += arr[++i];                                                          
                        }
                        str += $"\n{WhiteSpace}{SubSql(sub)}";
                    }
                }
                count--;               
                return $"{str}\n{WhiteSpace})";
            }
            else if (!string.IsNullOrEmpty(sql.Trim()))
            {
                str += $"\n{WhiteSpace}{sql}";
            }
            count--;
            str += $"\n{WhiteSpace}{end}";
            return str;
        }

        private string ComplexSub(string sql)
        {
            var str = string.Empty;
            var temp = sql.ReplaceIgnoreCase("and ", "\n AND ").ReplaceIgnoreCase("or ", "\n OR ");
            var arr = temp.Split('\n');
            for (int i = 0; i < arr.Length; i++)
            {
                var sub = arr[i];
                while (!IsFullStr(sub))
                {
                    if (i < arr.Length - 1) sub += arr[++i];                   
                }
                str += "\n" + WhiteSpace + sub;
            }
            return str;
        }

        private bool IsFullStr(string str)
        {
            int left = 0;
            int right = 0;
            bool mark = false;
            int num = 0;
            foreach (var item in str)
            {
                if (item.Equals('\''))
                {
                    mark = !mark;
                    num++;
                    continue;
                }
                if (mark) continue;
                if (item.Equals('(')) left++;
                if (item.Equals(')')) right++;
            }
            return left == right && num % 2 == 0;
        }

        private string UpdateCols(string sql)
        {
            var cols = sql.SubStr(0, sql.IndexOfIgnoreCase("where "));
            int num = 0;
            while (!IsFullStr(cols))
            {
                cols = sql.SubStr(0, sql.IndexOfCount("where ", ++num));
            }
            var str = "";
            if (cols.ContainsIgnoreCase("select ") 
                && !cols.SubStr(0,cols.IndexOf(")")).Contains("="))
            {
                var sub = cols.Substring(cols.IndexOfIgnoreCase("select"));               
                sub =sub.Substring(0, sub.LastIndexOf(")"));
                cols = cols.Substring(0, cols.IndexOf(")")).Trim();
                str = $"{WhiteSpace}({AddCols(cols.Substring(1))}";
                str += $"\n{WhiteSpace})= (";
                str += $"\n{WhiteSpace}{SelectSql(sub)}";
                str += $"\n{WhiteSpace})";              
            }
            else
            {
                str = AddCols(cols);
            }          
            str += $"{WhereSql(sql)}";                       
            return str;
        }

        private string WhiteSpace
        {
            get
            {
                var str = "";
                var num = count <= 0 ? 1 : count;
                for (int i = 0; i < num; i++)
                {
                    str += "\t";
                }
                return str;
            }
        }

        private string GetTxt(string sql, bool IsIn = false)
        {
            sql = sql.Substring(sql.IndexOf("(") + 1);
            sql = sql.Substring(0, sql.LastIndexOf(")")).Trim();
            if (sql.StartsWith("(") && sql.EndsWith(")") && IsIn) sql = sql.Substring(1, sql.Length - 2);
            return sql;
        }

        private string OnFormat(string sql)
        {
            if (sql.ContainsIgnoreCase("on "))
            {
                var str = WhiteSpace;
                if (!sql.StartsWithIgnoreCase("on "))
                {
                    str = sql.Substring(0, sql.IndexOfIgnoreCase("on ")) + "\n";
                    sql = sql.Substring(sql.IndexOfIgnoreCase("on "));
                }
                var arr = sql.SplitIgnoreCase("and");
                for (int i = 0; i < arr.Length; i++)
                {
                    var col = arr[i].Trim();
                    var comma = i == arr.Length - 1 ? "" : $"\n{WhiteSpace}AND";
                    str += $"{col}{comma}";
                }
                return str;
            }
            return sql;
        }

        private string GroupOrderFormat(string sql)
        {
            var str = sql.Substring(0, sql.IndexOfIgnoreCase("by ") + 2) + "\n";
            return str + AddCols(sql.Substring(sql.IndexOfIgnoreCase("by ") + 2));
        }

        private string AddCols(string sql, string[] arr = null)
        {
            var str = "";
            arr = arr ?? SplitContainsQuotation(sql);
            count++;
            for (int i = 0; i < arr.Length; i++)
            {
                var col = arr[i].Trim();
                var comma = i == arr.Length - 1 ? "" : ",\n";
                if (col.ContainsIgnoreCase("select ")
                    && !col.StartsWith("'") && !col.ContainsIgnoreCase("CONCAT"))
                {
                    var end = col.Substring(col.LastIndexOf(")"));
                    var sel = SelectSql(GetTxt(col, true));
                    var start = col.Substring(0, col.IndexOfIgnoreCase("select"));
                    if (start.LastIndexOf("(") != start.IndexOf("(")) end = ")" + end;
                    col = $"{start}\n{sel}\n{WhiteSpace}{end}";
                }
                else
                {
                    col = LenNewLine(col);
                }
                str += $"{WhiteSpace}{col}{comma}";
            }
            count--;
            return str;
        }

        private string LenNewLine(string sql)
        {
            if (sql.Contains("||")) return Concat(sql);          
            if (sql.Length > 150 && !sql.ContainsIgnoreCase("CONCAT"))
            {
                var str = "";
                var arr = sql.Split(' ');
                for (int i = 0; i < arr.Length; i++)
                {
                    var item = arr[i];
                    while (item.Length < 100 || !IsFullStr(item))
                    {
                        if (i < arr.Length - 1)
                        {
                            item += arr[++i];
                        }
                        else
                        {
                            break;
                        }
                    }
                    var comma = str.Equals("") ? "" : "\n" + WhiteSpace;
                    str += $"{comma}{item}";
                }
                return str;
            }
            return sql;
        }

        private string Concat(string sql)
        {
            var end = "";
            if (sql.ContainsIgnoreCase("as ") && !sql.ContainsIgnoreCase("case "))
            {
                end = sql.Substring(sql.IndexOfIgnoreCase("as "));
                sql = sql.Replace(end, "").Trim();
            }
            else if (sql.ContainsIgnoreCase("case "))
            {
                var start = sql.Substring(0, sql.IndexOfIgnoreCase("then ") + 4);
                end = sql.Substring(sql.IndexOfIgnoreCase("else "));
                sql = sql.Replace(start, "").Replace(end, "").Replace("||", ",").Trim();
                if (end.Contains("||"))
                {
                    var str=end.ReplaceIgnoreCase("ELSE ","");
                    str = str.Substring(0, str.IndexOfIgnoreCase("end"));
                    end = $"ELSE CONCAT({str.Replace("||",",")}) {end.Substring(end.IndexOfCount("end"))}";
                }
                return $"{start} CONCAT({sql}) {end}";
            }
            return $"CONCAT(\n{AddCols(null, Regex.Split(sql, "\\|\\|"))}\n{WhiteSpace}){end}";
        }

        public string[] SplitContainsQuotation(string value, bool IsFunc = false)
        {
            var list = new List<string>();
            if (!value.Contains(",")) return new string[] { value };
            do
            {
                var sub = value.SubStr(0, value.IndexOf(",")).Trim();
                int num = 0;
                while (!IsFullStr(sub))
                {
                    sub = value.SubStr(0, value.IndexOfCount(",", ++num)).Trim();
                }
                value = value.ReplaceFirst(sub, "").ReplaceFirst(",", "").Trim();
                if (sub.Contains("(") && sub.Contains(")") && sub.Length > 180
                    && !sub.StartsWith("'") && !sub.EndsWith("'") && IsFunc)
                {
                    sub = LengthFomart(sub, count, true);
                }
                list.Add(sub);
            }
            while (value.Contains(","));
            if (!string.IsNullOrEmpty(value)) list.Add(value);
            return list.ToArray();
        }
    }
}
